Public Class cCommissione
    Inherits Connessione

    Private _idCommissione As String

    Public Structure stIscritto
        Public id As String
        Public idAna As String
        Public Nominativo As String
        Public numero As String
        Public responsabile As Boolean
    End Structure

    Public Property idCommissione() As String
        Get
            Return _idCommissione
        End Get
        Set(ByVal Value As String)
            _idCommissione = Value
        End Set
    End Property

    Public Function elencoIscritti() As DataTable
        Return getDataTable("SELECT  id, idAna, Nominativo, Numero, tipo=case when responsabile=1 then 'Responsabile' else '' end FROM IscCommissioni WHERE idCommissione = " & _idCommissione)
    End Function

    Public Function elencoXEtichette() As DataTable
        Return getDataTable("SELECT '0' as  id, idAna, '0' as idProf, Nominativo FROM IscCommissioni WHERE idCommissione = " & _idCommissione)
    End Function

    Public Function nomeCommissione() As String
        Return getValore("select descrizione from commissioni where id=" & _idCommissione)
    End Function

    Public Sub salva(ByVal iscritto As stIscritto)
        Dim sql As String
        If iscritto.id = "" Then
            sql = "insert into iscCommissioni (idCommissione, idAna, Nominativo, Numero,responsabile) values (@idCommissione, @idAna, @Nominativo, @Numero,@responsabile)"
        Else
            sql = "update iscCommissioni set idCommissione=@idCommissione, idAna=@idAna, nominativo=@nominativo, numero=@numero, responsabile=@responsabile where id=" & iscritto.id
        End If

        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql, getConnection)
        cmd.Parameters.AddWithValue("@idCommissione", _idCommissione)
        cmd.Parameters.AddWithValue("@idAna", iscritto.idAna)
        cmd.Parameters.AddWithValue("@nominativo", iscritto.Nominativo)
        cmd.Parameters.AddWithValue("@numero", iscritto.numero)
        cmd.Parameters.AddWithValue("@responsabile", iscritto.responsabile)

        cmd.ExecuteNonQuery()
        cmd.Connection.Close()
    End Sub

    Public Sub elimina(ByVal id As String)
        eseguiSql("delete from iscCommissioni where id=" & id)
    End Sub

End Class

Public Class cConsiglio
    Inherits Connessione

    Public Structure stIscritto
        Public id As String
        Public idAna As String
        Public Nominativo As String
        Public numero As String
        Public ruolo As String
    End Structure



    Private _idConsiglio As String

    Public Property idConsiglio() As String
        Get
            Return _idConsiglio
        End Get
        Set(ByVal Value As String)
            _idConsiglio = Value
        End Set
    End Property

    Public Function elencoIscritti() As DataTable
        Return getDataTable("SELECT  id, idAna, Nominativo, Numero, ruolo FROM consiglieri WHERE idConsiglio = " & _idConsiglio)
    End Function

    Public Function elencoXEtichette() As DataTable
        Return getDataTable("SELECT '0' as  id, idAna, '0' as idProf, Nominativo FROM consiglieri WHERE idConsiglio = " & _idConsiglio)
    End Function

    Public Sub salva(ByVal iscritto As stIscritto)
        Dim sql As String
        If iscritto.id = "" Then
            sql = "insert into consiglieri (idConsiglio, idAna, Nominativo, Numero,ruolo) values (@idConsiglio, @idAna, @Nominativo, @Numero,@ruolo)"
        Else
            sql = "update consiglieri set idConsiglio=@idConsiglio, idAna=@idAna, nominativo=@nominativo, numero=@numero, responsabile=@ruolo where id=" & iscritto.id
        End If

        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql, getConnection)
        cmd.Parameters.AddWithValue("@idConsiglio", _idConsiglio)
        cmd.Parameters.AddWithValue("@idAna", iscritto.idAna)
        cmd.Parameters.AddWithValue("@nominativo", iscritto.Nominativo)
        cmd.Parameters.AddWithValue("@numero", iscritto.numero)
        cmd.Parameters.AddWithValue("@ruolo", iscritto.ruolo)

        cmd.ExecuteNonQuery()
        cmd.Connection.Close()
    End Sub

    Public Sub elimina(ByVal id As String)
        eseguiSql("delete from consiglieri where id=" & id)
    End Sub

    Public Sub creaConsiglio(ByVal dal As String, ByVal al As String)
        Dim sql As String = "insert into consigli (dal, al) values (@dal, @al)"
        Dim cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand(sql, getConnection)
        cmd.Parameters.AddWithValue("@dal", Utility.FormattaDataxDB(dal))
        cmd.Parameters.AddWithValue("@al", Utility.FormattaDataxDB(al))
        cmd.ExecuteNonQuery()
        cmd.Connection.Close()
    End Sub

End Class
